Create and Manage Datasets
For Digitise Forms Datasources and Imported Datasources, Datasets define database tables and their columns (along with the tables associated with imported Web APIs
An API is a set of functions and procedures allowing the creation of applications that access the features or data of an operating system, application, or other service.) within the Datasource(s) that you want to work with in your form. The columns specified in the Dataset can be linked to Elements on your form to allow the Elements to display data from the Datasource and update the Datasource with information entered into the form. We call this linking, 'mapping'.
For Imported Stored Procedure Datasources, a Dataset can either define the set of parameters which must be passed to the Stored Procedure when you execute it or a set of data columns the Stored Procedure will return as a Result Set.
For Digitise Forms Datasources, a Dataset defines the information to be stored in a specific database table in the Datasource and allows you to map Elements to the columns in the Dataset. You can create multiple Datasets within the same Datasource, if you want to, with each Dataset representing a separate table within the database. The Project Datasource is a default Digitise Forms Datasource which can be used to store data for your form in a single location. When you add a new Dataset to a Digitise Forms Datasource, the database table, and the database if necessary, will be created when you next publish your form. If you want a Digitise Forms Datasource to use Stored Procedures to update the database, you can enable the use of Stored Procedures within the Dataset's properties (see below).
For Imported Datasources (including Web APIs), a Dataset specifies the columns in a table within the remote database or remote datasource that you want to map to Elements on the form. Data in the columns you specify can be downloaded from the remote database or datasource and displayed in your form, and information entered into the form can be used to update the remote database. A Dataset can only contain columns from one table, but, if the remote database or datasource includes multiple tables, you can create a separate Dataset for each of the tables you need. You can only create one Dataset per table.
For Imported Stored Procedure Datasources, there are two types of Dataset. A Parameters Dataset defines the parameters which must be passed to the Stored Procedure when it is executed. This type of Dataset is only created when the Stored Procedure does not return any Result Sets but does require one or more parameters to be passed in when it is run. You can only have one Parameters Dataset. A Result Set Dataset specifies the columns which will be included in a Result Set returned to the form by the Stored Procedure. If the Stored Procedure returns multiple Result Sets, you can create a Dataset for each Result Set you want to use in your form. You will need a separate Dataset for each Result Set and so you can have multiple Result Set Datasets in a single Datasource. If a Stored Procedure returns one or more Result Sets and requires parameters to be passed to it, the parameters do not have a separate Dataset but are passed to the Stored Procedure via a SQL query (see below). Data fields in a Result Set Dataset can be input mapped to Elements on your form to display the data returned from the Stored Procedure. Data fields in a Parameters Dataset can be output mapped to Elements on your form to provide the parameter values to be passed to the Stored Procedure when it is executed.
For Digitise Forms Datasources and Imported Datasources, a Dataset also allows you to specify one or more SQL queries, which can be used to specify which records in the database you want to be downloaded. For example, you might only want to display information from records between certain dates or which relate to the person using the form.
Imported Stored Procedure Datasets can only have one SQL query, which is generated automatically by Form Studio and cannot be changed. This query is used to execute the Stored Procedure. If the Stored Procedure returns at least one Result Set, any parameters required to be passed into the Stored Procedure will be included in this query.
To create, view and manage Datasets, click on the Datasources tab in the Project Explorer.
Select the relevant Datasource.
Any existing Datasets for the selected Datasource will be displayed in the Form Design workspace area of the Form Studio window:
If Datasource or Dataset names are too long to be fully displayed, hovering your mouse pointer over a Datasource or a Dataset will display a tooltip showing the full name.
To open a Dataset, double-click on it or right-click and choose Open from the menu displayed.
The Dataset will open and display in the Form Design workspace:
The properties for the Dataset will be displayed in the Properties Pane.
Right-click in the Form Design workspace or on the Datasource in the Datasources tab.
Choose New Dataset.
A new, empty, Dataset will be created and displayed in the Form Design workspace.
Double-click the new Dataset or right-click on it and choose Open from the menu displayed.
Once you've added a Dataset to a Digitise Forms Datasource, you will need to define the data columns in the Dataset (see below). You may also want to edit the Dataset's properties.
This option is only available for Digitise Forms Datasources.
You can define and edit the data columns for an open Dataset under the Mappings tab.
An ID column will automatically be added as the first column and this will form the Dataset's Primary Key. By default the Primary Key will be given a data type of GUID. You can change this to be int with its Identity property set to True, if you prefer, from the drop-down list in its Type column under the Mappings tab, although we recommend you use the more secure GUID data type. You can extend the Primary Key if you want to by combining other columns with the ID column, but you cannot delete the ID column. New records created in the Datasource will automatically be assigned a unique identifier in the ID column.
To add a new column, click on the Add Column button:
Select the new entry in the list to highlight it.
You can now edit the column properties, for example to give the column a more meaningful name and specify what type of information will be stored in this column in the Type field. Note that a value of -1 in the Max Length property denotes that there is no restriction on the length of the data which can be stored in this column, except any limits imposed by SQL Server itself.
The SQL Name is the name the column will be given in the database, whereas the FX Name is the SQL name with any invalid characters replaced with '_'. The FX Name will automatically be created from the SQL Name and cannot be edited. The SQL Name can only contain A-Z, a-z, 0-9, '-' and '_', cannot start with a number or '-' and cannot contain spaces. Any '-'s used in a column name will be converted to '_' in the FX Name. When referencing a column within custom JavaScript, however, you may need to further convert the FX Name for JavaScript compatibility and other factors. The Code Name field shows the converted FX Name as you will need to use it in custom JavaScript. The name is case-sensitive and must be used in your custom JavaScript exactly as it appears in the Code Name column. Note that when adding a new column to a Dataset, the Code Name will only be displayed after you close the Dataset and re-open it (as seen in the picture above).
To include a column in the Dataset, you need to select the check box in the Mapped column at the beginning of the row.
To remove a column from the Dataset, uncheck its Mapped check box.
Repeat this process to build up all the columns you want in this Dataset.
You can edit the Dataset at any time to add or delete columns, rename columns, change the length of the data within a column or whether a column can contain Nulls etc. If you make structural changes, such as adding or deleting columns or changing the name of a column, you will need to check your data mappings afterwards and remap any Elements affected by your changes. If you have previously published the form, when you republish it, any changes which could affect your existing data will be listed and you can choose to update the existing databases or delete them and create new ones. We recommend that you take a backup of existing databases before publishing changes to your Datasources.
If you are comparing Dataset data fields to columns in a SQL database table, you will notice that the data types available in Form Studio don't exactly match the data types used in SQL Server. This is because Digitise Forms uses C#/.NET data types rather than SQL Server data types. If you're not sure what data type you need to select, you can find conversion tables online, such as the following Microsoft web page:
Note that when adding columns, you can't specify default column values - if you want to do this, you will need to create the database table outside Form Studio, e.g. using Microsoft's SQL Server Management Studio, and then import the database instead.
You can define and edit the data columns for an open Dataset under the Mappings tab.
An ID column will automatically be added as the first column and this will form the Dataset's Primary Key. By default the Primary Key will be given a data type of GUID. You can change this to be int with its Identity property set to True, if you prefer, from the drop-down list in its Type column under the Mappings tab, although we recommend you use the more secure GUID data type. You can extend the Primary Key if you want to by combining other columns with the ID column, but you cannot delete the ID column. New records created in the Datasource will automatically be assigned a unique identifier in the ID column.
To add a new column, click on the Add Column button:
Select the new entry in the list to highlight it.
You can now edit the column properties, for example to give the column a more meaningful name and specify what type of information will be stored in this column in the Type field. Note that a value of -1 in the Max Length property denotes that there is no restriction on the length of the data which can be stored in this column, except any limits imposed by SQL Server itself.
The SQL Name is the name the column will be given in the database, whereas the FX Name is the SQL name with any invalid characters replaced with '_'. The FX Name will automatically be created from the SQL Name and cannot be edited. The SQL Name can only contain A-Z, a-z, 0-9, '-' and '_', cannot start with a number or '-' and cannot contain spaces. Any '-'s used in a column name will be converted to '_' in the FX Name. When referencing a column within custom JavaScript, however, you may need to further convert the FX Name for JavaScript compatibility and other factors. The Code Name field shows the converted FX Name as you will need to use it in custom JavaScript. The name is case-sensitive and must be used in your custom JavaScript exactly as it appears in the Code Name column. Note that when adding a new column to a Dataset, the Code Name will only be displayed after you close the Dataset and re-open it (as seen in the picture above).
To include a column in the Dataset, you need to select the check box in the Mapped column at the beginning of the row.
To remove a column from the Dataset, uncheck its Mapped check box.
If you have more than one Dataset in a Datasource, you can configure Foreign Key relationships between the Datasets, if you want to. This allows you to link a column in one Dataset, called a child Dataset, to the Primary Key column of another Dataset, called the parent Dataset, and means that the child Dataset column will only be able to contain values which match values in the parent Dataset column. So, for example, if you try to add a record to the child table in which the Foreign Key column is blank or contains a value which doesn't match any of the Primary Key values in the parent, the update will fail and the record won't be added to the child table. You can only do this where both Datasets are in the same Datasource and both columns have the same data type. Foreign Key relationships may allow you to include data in the child Dataset in uploads and downloads of the parent Dataset, for more information see Handling Datasets with Foreign Keys.
To create a Foreign Key relationship, open the child Dataset and display the Mappings tab as shown above. For the column you want to be the child, drop down the Foreign Key Table list and select the Dataset you want to be the parent Dataset. Then select the parent column you want to link to from the Foreign Key Column list. Where Foreign Key relationships exist, the parent Dataset and column will be displayed in the Foreign Key Table and Foreign Key Column columns in the child Dataset.
Once you have defined columns for a Dataset, you can then supply actual data for the Dataset, if required. For example, if you are creating a Digitise Forms Datasource to supply your options for a drop-down list on the form, having specified your columns, you can then supply the values to appear within the list.
To enter data values, open the Dataset and then click on the Data tab.
A table will be displayed in the Form Design workspace, with a column for each of the columns in the Dataset:
If you have not yet entered any data, click in a column of the first row to enter a value into that column. You cannot enter data into the first data column, which always contains (auto), as this column is filled automatically with a unique ID.
You can use the Tab, Shift+Tab and Arrow keys to move between columns and rows.
Use Return to add a new row (each row equates to a record within the database table).
If you are entering data in the last row, you must press Return or click in a column in a different row to save your changes in this row, otherwise they will be lost.
Click in the first column of a row to select that row. A black triangle will be displayed in the first column, indicating this is the currently selected row.
Press Del, or right-click and choose Delete Row, to delete the currently selected row.
- Data is only stored in a database table when the database is created. If you later want to change the values stored, e.g. you want to add an extra item to a Check List or Radio List, you can do this in one of the following ways:
Delete the existing database and recreate it:
Edit the data in the appropriate Dataset and then change the name of the Dataset (see Dataset Properties below). The name change will automatically be applied to any relevant existing Element data mappings. Republish the form and Choose Drop and create new database when asked how you want your changes to be implemented.
This will delete the current database and create a new one, including storing your new data items. Since the original database will be deleted, any existing data in it, which won't be restored by your Datasource definitions, will be lost. We recommend, therefore, that you backup or extract the data before you make the changes.
If you want to retain the original name of the Dataset, you can rename the Dataset back to its original name and republish. This time, choose Upgrade existing data to apply your changes, as this will upgrade the existing database without deleting it.
Edit the data outside Form Studio, e.g. using Microsoft's SQL Server Management Studio.
Manage Datasets for Imported Datasources
With Imported Datasources you would normally create your required Datasets when you import the database. You can, however, later add new Datasets and change the columns included in your Datasets, should you need to.
To add or remove columns in an existing Dataset, open the Dataset and click on the Mappings tab:
Each Dataset represents an individual table in the remote database. The Dataset will contain all data columns in that table and you can choose which of the available columns you want to include in your project. Only the columns you include here will be available to your form, i.e. data in columns you don't select won't be downloaded to the form and hence can't be displayed or edited on the form and unselected columns won't be updated if you upload records to the remote database from the form.
- Only columns to which the user specified in the Datasource connection string has been granted SELECT access within the SQL Database will be included here. Any columns which the specified user doesn't have permission to SELECT will be ignored.
To include a column in the Dataset, you need to select the check box in the Mapped column at the beginning of the row.
To remove a column from inclusion in the Dataset, uncheck its Mapped check box. The column will not be deleted from the Dataset and can be re-added later, if needed, by reselecting the Mapped check box.
When you create a Dataset, all columns will have the Mapped check box checked. You can select or deselect all columns using the Map All Columns option just below the tab. Selecting the option will include all columns in the Dataset, deselecting it will remove all the columns from the Dataset (apart from the Primary Key column(s)).
You cannot edit the other columns under the Mappings tab, such as column name, data type etc. as these are read directly from the remote database.
- The SQL Name column shows you the names of the columns in the database, whereas the FX Name column shows the corresponding name with any invalid characters replaced with '_'s. When referencing a column within custom JavaScript, however, you may need to further convert the FX Name for JavaScript compatibility and other factors. For simplicity, the Code Name field shows the column name as you will need to use it in custom JavaScript. The name is case-sensitive and must be used in your custom JavaScript exactly as it appears in the Code Name column.
-
If you are comparing Dataset data fields to columns in the SQL database table, you will notice that the data types available in Form Studio don't exactly match the data types used in SQL Server. This is because Digitise Forms uses C#/.NET data types rather than SQL Server data types. If you're not sure what data type you need to select, you can find conversion tables online, such as the following Microsoft web page:
https://docs.microsoft.com/en-us/sql/relational-databases/
clr-integration-database-objects-types-net-framework/mapping-clr-parameter-data?
view=sql-server-ver15&redirectedfrom=MSDN&viewFallbackFrom=sql-server-2014
If you want to add a database table that you didn't include when you originally created the Datasource, right-click on the Datasource in the Datasources tab and choose Remap Tables from the menu displayed.
The table mapping dialog will be displayed:
allowing you to change the database tables included in your Datasource. Refer to the
You can also use this dialog box to delete Datasets or you can right-click on a Dataset in the Form Design workspace and choose Delete Dataset.
For Digitise Forms Datasources and Imported Datasources, in addition to specifying the data columns, a Dataset allows you to enter SQL queries to be used to select the data records to be downloaded from a remote database.
Imported Stored Procedure Datasets can only have one SQL query, which is generated automatically by Form Studio and cannot be changed. This query is used to execute the Stored Procedure.
The remainder of this section, therefore, only applies to Digitise Forms Datasources and Imported Datasources.
By default, two SQL queries are provided. Both download data contained in the columns specified in the Dataset; any other columns in the database are ignored.
The first query, SelectAll, downloads the data from all records in the database and is the default query used when a Datasource is marked to be downloaded automatically when the form loads (see Loads With Form property below). The first record will be made the current record and any Elements with input mappings to the Datasource will be updated.
The other query, SelectByKey, downloads records based on the Dataset's Primary Key. This query allows you to take a value from an Element on the form and then only download records whose Primary Key field matches against the value extracted from that Element. For example, you could provide a Text Box Element in which a user will enter their account number and then download the contact details record which has their account number as its Primary Key. This query is only generated for Datasets which have a Primary Key; read-only keyless Datasets will only have the SelectAll query.
In addition to these default queries, you can add your own custom queries if required. These queries can also provide the ability to match records to download against values extracted from Elements on the form, but do not need to match against Primary Keys.
You can use a custom query with a Dataset set to be automatically downloaded by the form (see below) or if you download the Dataset records from within an Element's Event, e.g. when a Button is clicked.
Note, that when adding your own queries, the query must return all columns specified in the Dataset, you cannot return a sub-set of the dataset columns. If you want to deal with a sub-set of the columns in the current Dataset, you will need to create a new Dataset, which in the case of a remote database will require you to create a new Imported Datasource. In addition, column names containing special characters, such as '_', '@' or '=' must be enclosed in square brackets, e.g. [myCol_1].
To view, create and edit SQL queries, open the relevant Dataset and click on the Mappings tab.
To enter a new query, click on the New Query... button.
A new query entry will be added under the Queries section at the bottom of the window.
Click in the first text box and then edit the name of the query to something which will allow you to identify this query later when you want to use it.
Click in the second text box and type in your SQL query.
The query must be a complete SQL SELECT statement. You cannot use SELECT DISTINCT - if you want to do this, you will need to create a SQL View on the database and use that within your SQL Query.
Within the SELECT statement, you must select your columns using the format [<SQL Name>] as <code name> where <SQL Name> is the name of the column as it appears in the database and <code name> is the Code Name version of the column name. The Code Name converts the original column name to a more JavaScript compatible format. You can check the Code Name for a column in the Code Name column under the Mappings tab. For example, if the original column name were Reported-Issue, this would be converted to reported_Issue for the Code Name and your SQL query would include: SELECT ... [Reported-Issue] as reported_Issue ...
The statement can include parameters within a WHERE clause allowing you to select records based on the current value of one or more Elements on the form, e.g. WHERE [Surname]=@Surname could be used to select records where the value in the Surname column matches the current value in a specified Element. Parameters take the form:
[<database column>] = @<parameter name> and [<database column>] = @<parameter name> ...
where <database column> is the column in the database which will be matched against the value substituted at runtime into the @<parameter name> part. The database column name should be the column name as it appears in the database and we recommend that you surround it by square brackets, [...], although this is generally only essential if the column names includes characters which are not letters or numbers. The parameter name can be any name of your choice, but we suggest you choose a name which will allow you to easily recognise the type of data it should contain. You can include multiple parameters, if required, as shown. If you only want one parameter, just include that parameter and omit the 'and' and subsequent parameters.
If you include one or more parameters in a SQL query, you can't use that query where a Dataset is automatically downloaded by the form, you can only use it within an Element's Event properties. Within the Event properties, you can specify the Element(s) on the form against whose value(s) you want to match records in the remote database. The value(s) in the Element(s) will be read before the data is downloaded and substituted into the SQL query in place of the appropriate @<parameter name>. The Elements must always contain suitable values matching the type of data expected in the specified columns within the records.
If you want to allow for a partial match within a parameter, you can use LIKE instead of = by specifying the parameter within your query using the following format:
[<database column>] LIKE '%' + @<parameter name> + '%'
This will match any record where the specified column contains the value passed in the parameter anywhere within the column's value. For example, suppose your remote data stores addresses including a column called postcode, specifying the following parameter would allow you to select records where the form user enters their exact postcode or only part of it:
[postcode] LIKE '%' + @customerpostcode + '%'
In this case, a user entering Z1, 1 1, 1ZZ, ZZ1 1ZZ etc. would find any records containing the postcode ZZ1 1ZZ.
Note that you cannot edit the default queries nor delete them. You can, however, edit or delete any custom queries that you add. You can edit a query in the standard Windows manner and to delete one click on the 
button at the end of its row.
When you open a Dataset, its properties will display in the Properties Pane.
The following properties are available:
Specify a name of your choice to identify this Dataset.
We recommend that you don't give your Datasets names which corresponds to any reserved words. Reserved words are words which are used internally within Digitise Forms or have a special meaning within JavaScript and generally cannot be used for user-definable items, such as a Dataset name. For example, you should not call a Dataset: submit, onsubmit, history or images, although SubmitData, CustHistory etc. could be used. For a list of typical reserved words see: https://www.w3schools.com/js/js_reserved.asp.
Note that right-clicking on this field and choosing Reset to default will clear the field.
For Imported Stored Procedure Datasources, if the Stored Procedure doesn't return a Result Set but requires parameters to be passed in, a Dataset called Parameters will be created. If the Stored Procedure returns one or more Result Sets, the corresponding Datasets will either be named after the database table(s) from which the data is extracted by the Stored Procedure or after the Stored Procedure, if you created a blank Dataset. You can rename these Datasets to something more meaningful, if you want to.
The name of the SQL Server schema to which the table belongs as it appears in the remote database. This is for information only and cannot be edited.
If you're not familiar with SQL Server Schemas you don't need to worry about this property but basically schemas are a way of organising the structure of a SQL Server database by allowing you to divide the objects making up a database, such as tables, into groups, each group, called a schema, having a different name. If you wanted to create two tables within a database which have the same name, each table would need to be within a different schema so that the two could be distinguished from each other. This property would allow you to check that this is the case.
The name of the database table as it appears in the remote database. This is for information only and cannot be edited.
This is the name of the Dataset's global table object as you need to refer to it within any custom JavaScript functions you add to the form. Note that the name spelling here is case-sensitive and must be used in your JavaScript exactly as it appears here.
This property indicates whether records in this Dataset will be updated automatically in response to updates in another Dataset because of a Foreign Key relationship between the two Datasets. The property is read-only and cannot be edited.
This property is only relevant for child Datasets which have a parent Dataset in the same Datasource and where the child Dataset only has one Foreign Key relationship consisting of a single database column. In other situations, this Property will always be unchecked.
The state of this property is dependent upon the state of the Submit Related Data property (see below) in the parent Dataset. If the parent has Submit Related Data selected, Is Updated with Parent will automatically be selected in the child Dataset. If the parent has Submit Related Data deselected, Is Updated with Parent will automatically be deselected in the child Dataset.
If Is Updated with Parent is selected, you don't need to provide for data updates for the current Dataset within the form. Records in this Dataset will automatically be included in data uploads defined for the parent Dataset. The name of the parent Dataset managing data operations for the current Dataset will be displayed in the Parent Dataset Name property below.
If Is Updated with Parent is not selected, you will need to upload records for the current Dataset at suitable points within the form separately from any other Dataset uploads, using the built-in Datasource operations or custom JavaScript within the page and Element Events. If you want to include child uploads with the parent uploads and the child Dataset is suitable, you can enable this by selecting Submit Related Data in the parent Dataset. The Is Updated with Parent property of the current Dataset will then automatically be selected to indicate that updates for this Dataset are being managed by the parent Dataset.
If Is Updated with Parent is selected, the following properties will not be displayed and will inherit their values from the parent Dataset: Loads With Form, Allow Write Access, Max Write Requests, Server-side validation, Sanitize data when storing and Update with Stored Procs.
If you are viewing or configuring a Dataset for a Recordset Element whose data structure is defined as static columns, this property will automatically be selected when the Recordset's Dataset is created using New Datasource from Form.
For more information about managing related Datasets, refer to the
If the Is Updated with Parent property above is selected, this property specifies the name of the related parent Dataset which is managing data uploads for the current Dataset. If the Is Updated with Parent property is not selected, this property won't be displayed. The property is read-only and cannot be edited.
For more information about managing related Datasets, refer to the
Selecting Loads with Form causes this Dataset to be downloaded automatically by the form the first time it is used within a form.
If this option is not selected you will need to load the Dataset at a suitable point within the form, using the built-in Datasource operations within the page and Element Events or within custom JavaScript.
Note that if you select this option but then do not input map any elements on the form to this Dataset, the data will not actually be downloaded and any output mappings to this Dataset will create a new Record.
When you select the Loads With Form property, a Default Query property will be displayed below it. This property allows you to specify the SQL query to be used to determine which records are downloaded and the order in which the records will be retrieved. By default the download will use the SelectAll query, but if you want to use a different query, click on the drop-down to display the list of available queries and select the one you want to use.
The queries listed here are taken from the Queries section under the Mappings tab and include the default SelectAll query and any custom queries you add which don't include parameters within the query. You cannot specify a query with parameters here because the dataset may be downloaded before the user has had an opportunity to enter any data into the form.
You can add, edit and delete custom queries as described above under Select records using a SQL Query. The list available here under the Default Query property will update automatically as you add, edit or delete custom queries. If there are any issues, the property will be set to use the default SelectAll query, e.g. if you delete the currently selected query or its contents.
You can use a custom query to restrict the records which are downloaded or to specify the order in which you want the records to be arranged, by including an ORDER BY clause in your query, e.g. SELECT ... ORDER BY [surname] ASC.
Selecting Loads With Form will also automatically select Allow Read Access (see below). Note, however, that subsequently deselecting this property won't automatically deselect Allow Read Access and you will need to manually deselect it if you no longer need it.
If the Is Updated with Parent property above is selected, the Loads With Form property will not be displayed and will take its value from the same property in the related parent Dataset.
If you are viewing or configuring a Dataset for a Recordset Element whose data structure is defined as static columns, the value of this property is inherited from the form Dataset and this option cannot be edited within the Recordset's Dataset. The form Dataset is the Dataset created using New Datasource from Form to output map non-Recordset Elements which aren't output mapped to another database.
For Imported Stored Procedures, this property is not available if the Stored Procedure requires parameters to be passed into it when it is executed or doesn't return a Result Set and therefore the property is not shown in the Properties Pane for these Datasets.
This property indicates whether the current Dataset is read-only or not. If the property is selected, the Dataset is read-only and this means that you can only input map this Dataset to Elements on the form; you can't output map it.
If the property is not selected, the Dataset can be used for both input and output mappings as required.
The property is for information only and cannot be edited. Its value is set automatically to selected when you import a Database table which doesn't have a Primary Key or for Imported Stored Procedures which return a Result Set.
This property is only displayed if another Dataset in the same Datasource includes a Foreign Key field which is linked to the Primary Key field of the current Dataset, i.e. the current Dataset is a parent Dataset for one or more child Datasets.
If you select this property, records in the related (child) Dataset will be updated automatically when corresponding records in the current Dataset are updated. The Loads With Form, Allow Write Access, Max Write Requests, Server-side validation, Sanitize data when storing and Update with Stored Procs properties in the related Dataset will be hidden and these properties will take their values from the current (parent) Dataset. Allow Read Access and Max Read Requests properties, however, will still appear under the child Dataset, unless you select Download with Parent for a Foreign Key column in the child Dataset. The property will be automatically selected if there are any child Datasets in the Datasource for which the current Dataset is a parent.
If this property is not selected, records in the child Dataset will need to be uploaded independently of the current Dataset and you will need to allow for the treatment of both Datasets separately within the form, using the built-in Datasource operations within the page and Element Events or custom JavaScript.
If this property is not displayed, the runtime behaviour will be the same as if it were displayed and unchecked.
For full details refer to the
The default value for this property is selected.
For Recordset Elements whose data structure is defined as static columns, this property will automatically be selected in the form Dataset. The form Dataset is the Dataset created using New Datasource from Form to output map non-Recordset Elements which aren't output mapped to an existing remote database.
Selecting this property enables the ability to read data into your form from the defined Dataset. If the property is not selected, data from this Dataset will not be readable by the form.
If you want to read data in from the Dataset and display it in your form, this option must be selected. If you don't need to read data in from this Dataset, you can deselect the option to reduce the potential for unauthorised access to the data defined in the Dataset from the form.
If the Loads With Form option above is selected, this option will be selected automatically.
If you are viewing or configuring a Dataset for a Recordset Element whose data structure is defined as static columns, the value of this property is inherited from the form Dataset and this option cannot be edited within the Recordset's Dataset. The form Dataset is the Dataset created using New Datasource from Form to output map non-Recordset Elements which aren't output mapped to an existing remote database.
For a child Dataset, related to a parent Dataset in the same Datasource by a Foreign Key field, this property will be displayed unless you select Download with Parent for any Foreign Key field in the child Dataset, in which case this property will be hidden and its value inherited from the parent Dataset.
For Imported Stored Procedures, this property will automatically be selected, if the Stored Procedure returns a Result Set. The property is read-only and cannot be changed. If the Stored Procedure only has a Parameters Dataset, the property is not relevant and will be hidden.
The Max Read Requests option below allows you to limit the number of attempts that can be made to read this Dataset within a session.
If the Allow Read Access option above is selected, this option allows you to set a limit on the number of times the Dataset can be read (or attempt to be read) within a single session, providing protection from malicious attackers attempting to overrun your form in a Denial of Service (DoS) or other attack.
If Allow Read Access is not selected, this option will automatically be set to 0 (zero).
When Allow Read Access is selected, this option will be set to -1, which means the number of read attempts is governed by the value you set in the Max APIs option when you publish your form. Remember that the Max APIs option covers all API requests throughout the form and not just read requests for this Dataset, so the number of available read attempts will depend upon what other actions the form performs and may vary each time the form is run. If you want to set a specific limit for a Dataset, you can specify it here in the Max Read Requests option. The value specified here is still subject to the overall limit set in Max APIs, and reads to this Dataset will no longer be allowed once the limit specified in Max Read Requests or Max APIs is reached, whichever comes first.
At runtime if the user hits the read limit, an HTTP 429 error will be generated, which will be displayed in the browser's standard Dev Tools' console or network tabs or, if you have Server Logging switched on, an error message will be added to the log.
For more information about things you might need to consider when specifying a limit here, see the Max APIs description.
The limit set here remains active from the time a form is loaded until its session expires, which by default is about an hour. Closing the browser tab or window doesn't end the session. If you attempt to use the form once the session has expired, read requests to the Digitise Forms Server component will fail with HTTP 401 errors.
You can also specify a limit on the number of write attempts which can be made to this Dataset, using the Max Write Requests option below.
This property will not be displayed if Allow Read Access (see above) is not available.
Selecting this property enables the ability to write data from your form to the defined Dataset using the Submit Button or Capita Pay360 Cart Elements, by using the built-in actions within Events or calling the submitForm or updateDatasource scripting functions within custom JavaScript. If the property is not selected, data cannot be written to this Dataset by the form using these methods.
If you want to write data from the form to the Dataset using any of the methods mentioned, this option must be selected. Otherwise you can deselect the option to reduce the potential for unauthorised write access to the Dataset from the form.
If you use the New Datasource from Form option to create a Datasource, this option will be selected automatically for the form Dataset.
If you have Elements output mapped to a Dataset which doesn't have this option selected, when you publish your form a warning will be displayed to alert you.
The Allow Write Access property will not be displayed in the following situations:
- the Is Updated with Parent property above is selected. In this case it will take its value from the same property in the parent Dataset.
- the Dataset has no Primary Key. In this case you cannot output map this Dataset and so the property is not relevant.
- where a Recordset Element has its data structure defined as static columns. In this case, the value of this property is inherited from the form Dataset. The form Dataset is the Dataset created using New Datasource from Form to output map non-Recordset Elements, which aren't already output mapped to an existing remote database.
or
- the Dataset represents an Imported Stored Procedure's Result Set.
For Imported Stored Procedures which only have a Parameters Dataset, this property will automatically be selected and is read-only, so cannot be changed.
If the Allow Write Access option above is selected, this option allows you to set a limit on the number of times the Dataset can be written to (or attempt to be written to) within a single session, providing protection from malicious attackers attempting to overrun your form in a Denial of Service (DoS) or other attack.
If Allow Write Access is not selected, this option will automatically be set to 0 (zero).
When Allow Write Access is selected, this option will be set to -1, which means the number of write attempts is governed by the value you set in the Max APIs option when you publish your form. Remember that the Max APIs option covers all API requests throughout the form and not just write requests for this Dataset, so the number of available write attempts will depend upon what other actions the form performs and may vary each time the form is run. If you want to set a specific limit for a Dataset, you can specify it here in the Max Write Requests option. The value specified here is still subject to the overall limit set in Max APIs, and writes to this Dataset will no longer be allowed once the limit specified in Max Write Requests or Max APIs is reached, whichever comes first.
At runtime if the user hits the write limit, an HTTP 429 error will be generated, which will be displayed in the browser's standard Dev Tools' console or, if you have Server Logging switched on, an error message will be added to the log.
For more information about things you might need to consider when specifying a limit here, see the Max APIs description.
The limit set here remains active from the time a form is loaded until its session expires, which by default is about an hour. Closing the browser tab or window doesn't end the session. If you attempt to use the form once the session has expired, write requests to the Digitise Forms Server component will fail with HTTP 401 errors.
You can also specify a limit on the number of read attempts which can be made to this Dataset, using the Max Read Requests option above.
If the Is Updated with Parent property above is selected, this property will be hidden and will take its value from the same property in the parent Dataset.
This property allows you to enable or disable server-side validation of data sent to the form's Digitise Forms Server component.
If Server-side validation is selected, the form's Digitise Forms Server component will check for any validation Rules applied to Elements which are output mapped to data items in this Dataset. If there are any, it will apply those same validations to data sent to it which purports to come from this Dataset, before it forwards the data on to the database table. If the data fails any of the validations, the Digitise Forms Server will return an HTTP 400 Bad Request error to the sender and will write a message to the Digitise Forms Server Log including the validation's specified Failure message, if there is one. If you are using Stored Procedures to update the database, server-side validation will be performed before the data is passed to the Stored Procedure.
If Server-side validation is not selected, validations specified for Elements output mapped to this Dataset will not be performed by the Digitise Forms Server.
By default, this property is selected. However, if Allow Write Access is selected and you deselect it, Server-side validation will automatically be deselected as well. Subsequently reselecting Allow Write Access, will reselect Server-side validation.
For Recordset Element Datasets whose data structure is defined as static columns, the value of this property is inherited from the form Dataset and this option cannot be edited within the Recordset's Dataset. The form Dataset is the Dataset created using New Datasource from Form to output map non-Recordset Elements which aren't output mapped to another database.
For Imported Stored Procedures, this property is not relevant if the Stored Procedure returns a Result Set and therefore is not shown in the Properties Pane for Datasets corresponding to a Stored Procedure Result Set.
If the Is Updated with Parent property above is selected, this property will be hidden and will take its value from the same property in the parent Dataset.
If you have upgraded a project created using Digitise Forms v1.4 or earlier, this property will be set to its default value of selected.
This property is not relevant for Imported Stored Procedures Datasources.
For Digitise Forms Datasources and Imported Datasources, it allows you to choose whether data being output from your form to a database should be sanitized before it is written to the database, to protect other systems, which could potentially share this data, from malicious scripts entered into your form.
If the property is selected, data will be sanitized before it is written to the database.
If the property is not selected, data will be written to the database as it is input into the form, without being sanitized.
By default the property is selected, unless you are upgrading a project created using Digitise Forms v1.4 or earlier, in which case the property will be set to unselected so that your forms continue to behave as they did before.
By default, this property is selected. However, if Allow Write Access (see above) is selected and you deselect it, Sanitize data when storing will automatically be deselected as well. Subsequently reselecting Allow Write Access, will reselect Sanitize data when storing.
If you are viewing or configuring a Dataset for a Recordset Element whose data structure is defined as static columns, the value of this property is inherited from the form Dataset and this option cannot be edited within the Recordset's Dataset. The form Dataset is the Dataset created using New Datasource from Form to output map non-Recordset Elements which aren't output mapped to an existing remote database.
For Imported Stored Procedures, this property is not relevant if the Stored Procedure returns a Result Set and therefore is not shown in the Properties Pane for Datasets corresponding to a Stored Procedure Result Set.
If the Is Updated with Parent property above is selected, this property will be hidden and will take its value from the same property in the parent Dataset.
This property is only relevant for Digitise Forms Datatsources.
If selected, when you publish this form, if there are one or more Elements output mapped to this Dataset, Form Studio will automatically generate Insert and Update Stored Procedures to be used to output data to this Dataset. The Stored Procedures will be saved to the same database used to hold the database table represented by the Dataset.
If this is the first time the form has been published since this Dataset was created, the Stored Procedures will be called:
-
dbo.<form name>_Insert
and
-
dbo.<form name>_Update
A third Stored Procedure will also be created called dbo.<form name>_Delete, but you can ignore this Stored Procedure as it is not used.
These Stored Procedures will automatically be executed by the Digitise Forms Server component, when you output data from the form, using a Submit Button, a Submit form or Update Datasource action within an Event or a call to submitForm or updateDatasource within custom JavaScript. Within your form, you handle the Dataset in the same way whether Update with Stored Procs is selected or not; the only difference is whether data is passed directly to the database from the Digitise Forms Server component or via Stored Procedures. Digitise Forms will use the appropriate Stored Procedure automatically, you don't need to instruct it which to use when.
The publish process will generate basic skeleton Stored Procedures which will perform an insert or update action when executed. If you are familiar with SQL Server and are able to edit these Stored Procedures, you can do so if you want to, for example to add extra data validation.
If you have already published this form with this Dataset at least once before, and you have made changes to the Dataset since you last published, the publish process will display a dialog box titled: Database migration for datasource <datasource name>. This dialog will list any changes which need to be made to the Stored Procedures and provide the names of the new Stored Procedures it can create to include the changes. At the bottom of the dialog box you will find three buttons giving you different options for how to proceed:
-
Drop and Create new database
If you choose this button, Form Studio will delete the current database represented by the changed Dataset's parent Datasource and re-create it from scratch. Any data contained within the database will be lost and new Stored Procedures will be created, deleting any changes you have made to the Stored Procedures. Three Stored Procedures will be created and named as if this were the first time the form had been published (see above).
-
Upgrade existing database
If you choose this button, Form Studio will make the changes listed to the current database. Data already stored in the database will not be lost. In addition, it will create new versions of the Insert and Update Stored Procedures and add these to the database leaving your previous Stored Procedures untouched. This means that if you have made changes to the Stored Procedures, you will need to modify the new Stored Procedures to add in the same changes. You can check your previous Stored Procedures as these will still be present. The names of the new Stored Procedures are displayed in the Data migration dialog box, so you should make a note of the new names before choosing this button. The new names will be in the format: dbo.<form name>_<action>_Migration<n>, where <action> will be Insert or Update and <n> represents a consecutive sequence of numbers, starting with 2, indicating the number of migrations undertaken. Your form will automatically use the latest versions of the Stored Procedures.
Note that the Delete Stored Procedure is not updated, as it isn't used.
-
- If you have separate environments, e.g. test, development and live, with their own SQL Server instances, the Stored Procedures for the same form in each environment may have different migration numbers at the end of the Stored Procedure names. This is because the migration number is specific to the SQL Server instance and so in each instance, the number depends on the number of times the Stored Procedures have been created in that particular instance. You don't need to change anything within your form when you publish to a different environment, Digitise Forms will automatically use the correct version of the Stored Procedure in each.
-
Cancel publish
Choosing this button will cancel the publish, without making any changes to the Stored Procedures or database.
If you have previously published this form with this Dataset but with Update with Stored Procs unselected and then you select Update with Stored Procs and republish, the Database migration dialog described above will be displayed listing the changes that will be made if you publish and giving you the same three options: Drop and Create new database, Upgrade existing database and Cancel Publish. These options work in basically the same way as described above. Note, however, that in this situation, the Delete Stored Procedure isn't created.
If Update with Stored Procs is not selected, Form Studio won't create Stored Procedures to be used to send data from the form to the Dataset. New records and updates will be passed directly to the SQL Server instead.
If you have previously published this form with Update with Stored Procs selected, and then you deselect Update with Stored Procs and republish, the Database migration dialog box, described earlier, will be displayed explaining that if you continue to publish, the current Stored Procedures will be deleted. The same three buttons mentioned above are available:
-
Drop and Create new database
Choosing this button, will delete the current database represented by the changed Dataset's parent Datasource and re-create it from scratch. Any data contained within the database will be lost. Any previous Stored Procedures will also be deleted.
-
Upgrade existing database
Choosing this button will not delete the current database and any existing data will be preserved. The existing Stored Procedures for this Datasource, however, will be deleted.
-
Cancel publish
Choosing this button will cancel the publish, without making any changes to the Stored Procedures or database.
If the Is Updated with Parent property above is selected, this property will be hidden and will take its value from the same property in the parent Dataset.
When you have finished editing a Dataset, to close the Dataset details page, click on a Datasource name in the Datasources tab or click on the Pages tab and select a page. You can also open another item from the Projects tab which will close the Dataset details and open the new item in the Form Design workspace.
When you have finished making changes, save your Project to save the form.
See also: